package com.gugee.tiktok.common.model.dal.ranking;

import com.gugee.tiktok.common.model.ranking.RankingFollowers;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface RankingFollowersRepository extends CrudRepository<RankingFollowers,Long>, RankingFollowersExtRepository {

    List<RankingFollowers> findByBloggerTypeAndRankingTypeAndBelong(long bloggerType,long rankingType,int belong);


    @Query("SELECT " +
            "b.id blogger_id,b.o_uid uid,b.o_nickname nickname,b.o_unique_id unique_id,b.o_avatar168 avatar," +
            "r.blogger_type,r.ranking_type,b.with_fusion_shop_entry,b.bio_url,b.bio_email," +
            "b.o_follower_count follower,SUM(r.follower_growth) follower_growth,b.enterprise_verify_reason,b.custom_verify,b.o_region region," +
            "IFNULL(ROUND(sum( b.o_avg_likes + b.o_avg_share + b.o_avg_reviews )/ sum( b.o_avg_views )* 100,1),0) engagement_rate " +
            "FROM ranking r left join blogger_main b on r.uid = b.o_uid " +
            "WHERE r.blogger_type = :bloggerType and r.ranking_type = :rankingType and r.belong  BETWEEN :firstDate and :lastDate " +
            "group by r.uid " +
            "order by SUM(r.follower_growth) desc")
    List<RankingFollowers> findRankingFollowers(long bloggerType, int rankingType, int firstDate, int lastDate);
}
